package com.wanmait.hotelManage.dao;

import java.awt.image.RescaleOp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.Pager;
import com.wanmait.hotelManage.vo.Register;
import com.wanmait.hotelManage.vo.Room;
import com.wanmait.hotelManage.vo.RoomType;
import com.wanmait.hotelManage.vo.Roomimages;

public class RoomDAO {
	private RoomDAO() {};
	private static RoomDAO roomdao=new RoomDAO();
	public static RoomDAO getRoomdao() {
		return roomdao;
	}
	/**
	 * 更具页数进行分页全部查找
	 * @param pager
	 * @return
	 */
	public List<Room> findall(Pager pager){
		List<Room>rooms=new ArrayList<Room>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT room.id,room.number,room.check_in,room_type.title,room_type.description,room.room_type_id, ");
		sb.append(" room_type.price,room_type.discount_price,room_type.hot,room_type.id a,room_type.enable,");
		sb.append(" room_type.input_time,room_type.pic");
		sb.append(" FROM room,room_type ");
		sb.append(" WHERE room.room_type_id=room_type.id AND room_type.enable=1 order BY number limit ?,? ");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			Room room=null;
			RoomType rt=null;
			while(rs.next()) {
				room=new Room();
				room.setId(rs.getInt("id"));
				room.setNumber(rs.getString("number"));
				room.setCheckIn(rs.getBoolean("check_in"));
				rt=new RoomType();
				rt.setId(rs.getInt("room_type_id"));
				rt.setTitle(rs.getString("title"));
				rt.setDescription(rs.getString("description"));
				rt.setPrice(rs.getDouble("price"));
				rt.setDiscountPrice(rs.getDouble("discount_price"));
				rt.setHot(rs.getBoolean("hot"));
				rt.setEnable(rs.getBoolean("room_type.enable"));
				rt.setInputTime(rs.getTimestamp("input_time"));
				rt.setPic(rs.getString("room_type.pic"));
				room.setRoomtype(rt);
				rooms.add(room);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return rooms;
	}
	/**
	 * 分页查找，状态条件
	 * @param pager
	 * @param flag
	 * @return
	 */
	public List<Room> findall(Pager pager,boolean flag){
		List<Room>rooms=new ArrayList<Room>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT room.id,room.number,room.check_in,room_type.title,room_type.description,room.room_type_id, ");
		sb.append(" room_type.price,room_type.discount_price,room_type.hot,room_type.id a,room_type.enable,");
		sb.append(" room_type.input_time,room_type.pic");
		sb.append(" FROM room,room_type ");
		sb.append(" WHERE room.room_type_id=room_type.id AND room_type.enable=1 and check_in=? order BY number limit ?,? ");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setBoolean(1, flag);
			ps.setInt(2, pager.getStart());
			ps.setInt(3, pager.getPageSize());
			rs=ps.executeQuery();
			Room room=null;
			RoomType rt=null;
			while(rs.next()) {
				room=new Room();
				room.setId(rs.getInt("id"));
				room.setNumber(rs.getString("number"));
				room.setCheckIn(flag);
				rt=new RoomType();
				rt.setId(rs.getInt("room_type_id"));
				rt.setTitle(rs.getString("title"));
				rt.setDescription(rs.getString("description"));
				rt.setPrice(rs.getDouble("price"));
				rt.setDiscountPrice(rs.getDouble("discount_price"));
				rt.setHot(rs.getBoolean("hot"));
				rt.setEnable(rs.getBoolean("room_type.enable"));
				rt.setInputTime(rs.getTimestamp("input_time"));
				rt.setPic(rs.getString("room_type.pic"));
				room.setRoomtype(rt);
				rooms.add(room);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return rooms;
	}
	/**
	 * 添加
	 * @param number
	 * @param chclick
	 */
	public void insert(String number, Integer chclick) {
		String sql="insert into room(number,room_type_id) values(?,?)";
		DBHelper.getDBHelper().executeUpdate(sql, number,chclick);
	}
	/**
	 * 根据id找对象，多表连接
	 * @param id
	 * @return
	 */
	public Room findById(Integer id) {
		Room room=null;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from room,room_type where room.id=? and room.room_type_id=room_type.id";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			RoomType roomtype=null;
			if(rs.next()) {
				room=new Room();
				room.setId(id);
				room.setNumber(rs.getString("number"));
				room.setCheckIn(rs.getBoolean("check_in"));
				roomtype=new RoomType();
				roomtype.setId(rs.getInt("room_type_id"));
				room.setRoomtype(roomtype);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return room;
	}
/**
 * 更新
 * @param id
 * @param flag
 * @param id1
 */
	public void update(Integer id,Boolean flag,Integer id1) {
		String sql="update room set room_type_id=?,check_in=? where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, id,flag,id1);
		
	}
	/**
	 * 批量删除
	 * @param id
	 */
	public void dels(String id) {
		String sql="delete from room where id in("+id+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	/**
	 * 普通删除
	 * @param id
	 */
	public void del(Integer id) {
		String sql="delete from room where id =?";
		DBHelper.getDBHelper().executeUpdate(sql, id);
		
	}
	/**
	 * 找状态
	 * @param id
	 * @return
	 */
	public Boolean findcheck(Integer id) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select check_in from room where id=?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
					rs.next();
					Boolean id1=rs.getBoolean("check_in");
					return id1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return null;
	}
	/**
	 * 查找romm_type_id
	 * @param id
	 * @return
	 */
	public Integer findRoomTypeid(Integer id) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select room_type_id from room where id=?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
					rs.next();
					Integer id1=rs.getInt("room_type_id");
					return id1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return null;
	}

	//退房修改room的状态为空闲
	public void update(int roomId) {
		// TODO Auto-generated method stub
		String sql="update room set check_in=0 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, roomId);
	}
	/**
	 * 找房间编号和id
	 * @param id
	 * @return
	 */
	public List<Room> findByroomTypeId(Integer id) {
		List<Room>rooms=new ArrayList<Room>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from room where room_type_id=? and check_in=0";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			Room room=null;
			while(rs.next()) {
				 room=new Room();
				 room.setId(rs.getInt("id"));
				room.setNumber(rs.getString("number"));
				rooms.add(room);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return rooms;
	}
//	public static void main(String[] args) {
//		List<Room>r=RoomDAO.getRoomdao().findByroomTypeId(1);
//		for (Room room : r) {
//			System.out.println(room.getNumber());
//		}
//	}
	/**
	 * 找number属性
	 * @param roomTypeId
	 * @return
	 */
	public String findbynumber(Integer roomTypeId) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from room where id=?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, roomTypeId);
			rs=ps.executeQuery();
			if(rs.next()) {
				String number=rs.getString("number");
				return number;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return null;
	}
	/**
	 * 更新room的入住状态
	 * @param register
	 */
	public void updateCheckIn(Register register) {
		String sql="update room set check_in=1 where number=?";
		DBHelper.getDBHelper().executeUpdate(sql, register.getRoom().getNumber());
	}
	/**
	 * 搜索按钮点击用roomTypeId进行查找
	 * @param roomTypeId
	 * @return
	 */
	public List<Room> findNumberAndId(Integer roomTypeId,Boolean check){
		List<Room>rooms=new ArrayList<Room>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT * FROM room,room_type where room.room_type_id=room_type.id AND room_type.id=? and check_in=?";
		
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, roomTypeId);
			ps.setBoolean(2, check);
			rs=ps.executeQuery();
			Room room=null;
			RoomType rt=null;
			while(rs.next()) {
				room=new Room();
				room.setId(rs.getInt("room.id"));
				room.setNumber(rs.getString("number"));
				room.setCheckIn(check);
				rt=new RoomType();
				rt.setId(roomTypeId);
				rt.setTitle(rs.getString("title"));
				rt.setDescription(rs.getString("description"));
				rt.setPrice(rs.getDouble("price"));
				rt.setDiscountPrice(rs.getDouble("discount_price"));
				rt.setHot(rs.getBoolean("hot"));
				rt.setEnable(rs.getBoolean("room_type.enable"));
				rt.setInputTime(rs.getTimestamp("input_time"));
				rt.setPic(rs.getString("room_type.pic"));
				room.setRoomtype(rt);
				rooms.add(room);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return rooms;
	}
	public List<Room> findNumberAndIds(int parseInt) {
		List<Room>rooms=new ArrayList<Room>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT * FROM room,room_type where room.room_type_id=room_type.id AND room_type.id=?";
		
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, parseInt);
			rs=ps.executeQuery();
			Room room=null;
			RoomType rt=null;
			while(rs.next()) {
				room=new Room();
				room.setId(rs.getInt("room.id"));
				room.setNumber(rs.getString("number"));
				room.setCheckIn(rs.getBoolean("check_in"));
				rt=new RoomType();
				rt.setId(parseInt);
				rt.setTitle(rs.getString("title"));
				rt.setDescription(rs.getString("description"));
				rt.setPrice(rs.getDouble("price"));
				rt.setDiscountPrice(rs.getDouble("discount_price"));
				rt.setHot(rs.getBoolean("hot"));
				rt.setEnable(rs.getBoolean("room_type.enable"));
				rt.setInputTime(rs.getTimestamp("input_time"));
				rt.setPic(rs.getString("room_type.pic"));
				room.setRoomtype(rt);
				rooms.add(room);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return rooms;
	}
//	public static void main(String[] args) {
//		List<Room>l=RoomDAO.getRoomdao().findNumberAndId(1);
//		for (Room room : l) {
//			System.out.println(room.getNumber());
//		}
//	}
	
	public Room findNumber(String id) {
		Room room=null;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT * FROM room,room_type where room.room_type_id=room_type.id AND room.number=?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, id);
			rs=ps.executeQuery();
			RoomType rt=null;
			if(rs.next()) {
				room=new Room();
				room.setId(rs.getInt("room.id"));
				room.setNumber(id);
				room.setCheckIn(rs.getBoolean("check_in"));
				rt=new RoomType();
				rt.setId(rs.getInt("room_type.id"));
				rt.setTitle(rs.getString("title"));
				rt.setDescription(rs.getString("description"));
				rt.setPrice(rs.getDouble("price"));
				rt.setDiscountPrice(rs.getDouble("discount_price"));
				rt.setHot(rs.getBoolean("hot"));
				rt.setEnable(rs.getBoolean("room_type.enable"));
				rt.setInputTime(rs.getTimestamp("input_time"));
				rt.setPic(rs.getString("room_type.pic"));
				room.setRoomtype(rt);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return room;
		
	}
//	public static void main(String[] args) {
//		Room room=RoomDAO.getRoomdao().findNumber("6004");
//		System.out.println(room.getRoomtype().getDiscountPrice());
//	}
	//根据房型id查询剩余房间
	public List<Room> findRoomByRoomTypeId(Integer id) {
		// TODO Auto-generated method stub
		List<Room> rooms=new ArrayList<Room>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from room where room_type_id = ? and check_in=0";
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			while(rs.next()) {
				Room room=new Room();
				room.setId(rs.getInt("id"));
				room.setCheckIn(false);
				room.setNumber(rs.getString("number"));
				
				
				rooms.add(room);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}

		return rooms;
	}
	public Integer findbyNumber(String number) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from room where number=?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, number);
			rs=ps.executeQuery();
			if(rs.next()) {
				return rs.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return null;
	}
	
}
